package com.imooc.common._this.poi;

import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.junit.Test;

import java.util.*;

@Slf4j
public class PoiTest {

    private static final Integer START_SERIAL_POS = 4;
    private static ExcelReader reader = ExcelUtil.getReader(FileUtil.file("excel/12月份第9批型材订单生产计划.xlsx"), 0);
    private Map<String, Object> cache = new HashMap<>();

    @Test
    public void run3() {
        Desc desc = packDesc(reader);
        System.out.println(JSONUtil.toJsonStr(desc));
    }

    @Test
    public void run4(){
        CopperBarExcelVO vo = new CopperBarExcelVO();
        Desc desc = packDesc(reader);
        vo.setFixed(desc.getFixed().getContent());
        vo.setEndDate(desc.getProEndDate().getContent());
        vo.setOrderDate(desc.getOrderDate().getContent());
        vo.setProBatchNo(desc.getProBatchNo().getContent());
        vo.setProNo(desc.getProNo().getContent());
        vo.setSalesNo(desc.getSalesNo().getContent());
        vo.setStartDate(desc.getProStartDate().getContent());
        List<CopperBarExcelVO.MaterialsInfo> materialsInfos = new ArrayList<>();

        List<LineInfo> lineInfos = packLineInfo();
        lineInfos.forEach(item->{
            CopperBarExcelVO.MaterialsInfo materialsInfo = new CopperBarExcelVO.MaterialsInfo();
            materialsInfo.setMaterialsNo(item.getMaterialsNo().getContent());
            materialsInfo.setStandard("");
            materialsInfo.setSpecifications("");
            materialsInfo.setSerialNumber(item.getSerialNumber().getContent());
            materialsInfo.setNumber(item.getNumber().getContent());
            materialsInfo.setMaterialsName(item.getMaterialsName().getContent());
            materialsInfo.setLength(item.getLength().getContent());
            materialsInfo.setMaterialId("");
            materialsInfos.add(materialsInfo);
        });
        vo.setDataSource(materialsInfos);
        System.out.println(JSONUtil.toJsonStr(vo));
    }


    private Desc packDesc(ExcelReader reader) {
        Desc desc = new Desc();
        /** 设置订单日期 **/
        Cell cell = reader.getCell(Desc.OrderDate.colNum, Desc.rowNum);
        Date _orderDate = cell.getDateCellValue();
        Desc.OrderDate orderDate = new Desc.OrderDate();
        orderDate.setContent(_orderDate);
        desc.setOrderDate(orderDate);

        /** 设置生产批次号 **/
        cell = reader.getCell(Desc.ProBatchNo.colNum, Desc.rowNum);
        String _proBatchNo = cell.getStringCellValue();
        Desc.ProBatchNo proBatchNo = new Desc.ProBatchNo();
        proBatchNo.setContent(_proBatchNo);
        desc.setProBatchNo(proBatchNo);
        /** 设置生产单号 **/
        cell = reader.getCell(Desc.ProNo.colNum, Desc.rowNum);
        String _proNo = cell.getStringCellValue();
        Desc.ProNo proNo = new Desc.ProNo();
        proNo.setContent(_proNo);
        desc.setProNo(proNo);
        /** 销售单号 **/
        cell = reader.getCell(Desc.SalesNo.colNum, Desc.rowNum);
        String _saleNo = cell.getStringCellValue();
        Desc.SalesNo salesNo = new Desc.SalesNo();
        salesNo.setContent(_saleNo);
        desc.setSalesNo(salesNo);
        /** 生产日期 **/
        cell = reader.getCell(Desc.ProStartDate.colNum, Desc.rowNum);
        Date _proStartDate = cell.getDateCellValue();
        Desc.ProStartDate proStartDate = new Desc.ProStartDate();
        proStartDate.setContent(_proStartDate);
        desc.setProStartDate(proStartDate);
        /** 交付日期 **/
        cell = reader.getCell(Desc.ProEndDate.colNum, Desc.rowNum);
        Date _proEndDate = cell.getDateCellValue();
        Desc.ProEndDate proEndDate = new Desc.ProEndDate();
        proEndDate.setContent(_proEndDate);
        desc.setProEndDate(proEndDate);

        /** 定尺 **/
        cell = reader.getCell(Desc.Fixed.colNum, Desc.rowNum);
        boolean _fixed = StrUtil.equals("是", cell.getStringCellValue());
        Desc.Fixed fixed = new Desc.Fixed();
        fixed.setContent(_fixed);
        desc.setFixed(fixed);
        return desc;
    }

private List<LineInfo> packLineInfo(){
    List<LineInfo> lineInfos = new ArrayList<>();
    Row row = reader.getCell(0, START_SERIAL_POS).getRow();
    int rowNum = row.getRowNum();
    LineInfo lineInfo = packLineInfo(reader, rowNum);
    lineInfos.add(lineInfo);
    while (hasNexted(row, reader)) {
        row = nextRow(row, reader);
        rowNum = row.getRowNum();
        lineInfo = packLineInfo(reader, rowNum);
        lineInfos.add(lineInfo);
    }
    return lineInfos;
}
    private LineInfo packLineInfo(ExcelReader reader, Integer rowNum) {
        LineInfo lineInfo = new LineInfo();
        /** 序号 **/
        Cell cell = reader.getCell(LineInfo.SerialNumber.colNum, rowNum);
        int _serialNumber = (int) cell.getNumericCellValue();
        LineInfo.SerialNumber serialNumber = new LineInfo.SerialNumber();
        serialNumber.setContent(_serialNumber);
        lineInfo.setSerialNumber(serialNumber);

        /** 长度 **/
        cell = reader.getCell(LineInfo.Length.colNum, rowNum);

        double _length = Double.valueOf(cell.toString());
        LineInfo.Length length = new LineInfo.Length();
        length.setContent(_length);
        lineInfo.setLength(length);

        /** 根数 **/
        cell = reader.getCell(LineInfo.Number.colNum, rowNum);
        int _number = Integer.valueOf(cell.toString());
        LineInfo.Number number = new LineInfo.Number();
        number.setContent(_number);
        lineInfo.setNumber(number);

        /** 物料名称 **/
        cell = reader.getCell(LineInfo.MaterialsName.colNum, rowNum);
        String _materialsName = null;
        try {
            _materialsName = cell.getStringCellValue();
            if (StrUtil.isBlank(_materialsName)) {
                _materialsName = (String) cache.get("materialsName");
            } else {
                cache.put("materialsName", _materialsName);

            }
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            _materialsName = (String) cache.get("materialsName");

        }
        LineInfo.MaterialsName materialsName = new LineInfo.MaterialsName();
        materialsName.setContent(_materialsName);
        lineInfo.setMaterialsName(materialsName);

        /** 物料编号 **/
        cell = reader.getCell(LineInfo.MaterialsNo.colNum, rowNum);
        Long _materialsNo = null;
        try {
            _materialsNo = (long) cell.getNumericCellValue();
            if (_materialsNo == 0l) {
                _materialsNo = (Long) cache.get("materialsNo");
            } else {
                cache.put("materialsNo", _materialsNo);
            }

        } catch (Exception e) {
            log.error(e.getMessage(), e);
            _materialsNo = (Long) cache.get("materialsNo");
        }
        LineInfo.MaterialsNo materialsNo = new LineInfo.MaterialsNo();
        materialsNo.setContent(_materialsNo);
        lineInfo.setMaterialsNo(materialsNo);
        return lineInfo;
    }

    private Boolean hasNexted(Row row, ExcelReader reader) {
        try {
            row = nextRow(row, reader);
            Cell cell = row.getCell(0);
            if (cell == null || !NumberUtil.isNumber(cell.toString())) {
                return false;
            } else {
                return true;
            }
        } catch (Exception e) {
            return false;
        }

    }

    private Row nextRow(Row row, ExcelReader reader, Integer... steps) {
        int rowNum = row.getRowNum();
        if (steps.length > 0 && steps[0] > 0) {
            rowNum += steps[0];
            Cell cell = reader.getCell(0, rowNum);
            return cell.getRow();
        } else {
            Cell cell = reader.getCell(0, ++rowNum);
            return cell.getRow();
        }
    }

    private Cell nextCol(Cell cell, ExcelReader reader, Integer... steps) {
        int columnIndex = cell.getColumnIndex();
        int rowNum = cell.getRow().getRowNum();
        if (steps.length > 0 && steps[0] > 0) {
            columnIndex += steps[0];
            return reader.getCell(columnIndex, rowNum);
        } else {
            return reader.getCell(++columnIndex, rowNum);
        }

    }

}
